﻿using System;
using System.Collections.Generic;
using System.Text;
using Pixysoft.Framework.XSchEmA.Entity;

namespace Pixysoft.Framework.XSchEmA.Sql
{
    class SQLiteSqlBuilder : ISqlBuilder
    {
        private static volatile SQLiteSqlBuilder instance;

        private static object syncRoot = new Object();

        public static SQLiteSqlBuilder Instance
        {
            get
            {
                if (instance == null)
                {
                    lock (syncRoot)
                    {
                        instance = new SQLiteSqlBuilder();
                    }
                }

                return instance;

            }
        }

        public string CreateTable(DatabaseSchema database, Pixysoft.Framework.XSchEmA.Entity.TableSchema table)
        {

            //发现auto increment 立刻建立主键 然后其他的 autoincrement / pk 就屏蔽掉

            bool hasAutoIncrement = false;

            StringBuilder builder = new StringBuilder();
            builder.Append("CREATE TABLE " + table.TableName);
            builder.Append("(");
            foreach (ColumnSchema column in table.Columns)
            {
                builder.Append(" " + column.ColumnName + " ");

                if (column.DataType.IsAutoIncrement && !hasAutoIncrement)
                {
                    hasAutoIncrement = true;

                    builder.Append("INTEGER");
                    builder.Append(CreateNullable(column));
                    builder.Append(" PRIMARY KEY AUTOINCREMENT");
                }
                else if (column.DataType.IsAutoIncrement && hasAutoIncrement)
                {
                    //自增退化为整数

                    builder.Append("INTEGER");
                    builder.Append(CreateNullable(column));
                }
                else
                {
                    builder.Append(GetDataType(column.DataType));
                    builder.Append(CreateNullable(column));
                }

                builder.Append(",");
            }

            builder.Remove(builder.Length - 1, 1);

            PrimaryKeySchema pk = database.GetPrimaryKey(table);

            if (!hasAutoIncrement && pk != null)
            {
                //add pk

                builder.Append(string.Format(", PRIMARY KEY({0})", SchemaUtility.ListToString<string>(pk.ColumnNames)));
            }

            //foreign key

            foreach (ForeignKeySchema fk in database.GetForeignKeys(table))
            {
                builder.Append(string.Format(", FOREIGN KEY({0}) REFERENCES {1}({2})",
                    SchemaUtility.ListToString<string>(fk.FkColumnNames),
                    fk.PkTableName,
                    SchemaUtility.ListToString<string>(fk.PkColumnNames)));
            }

            builder.Append(")");

            return builder.ToString();
        }

        protected string CreateNullable(ColumnSchema column)
        {
            if (column.IsNullable)
                return "";
            else
                return " NOT NULL";
        }

        public string DropTable(Pixysoft.Framework.XSchEmA.Entity.TableSchema table)
        {
            if (table == null)
                return null;

            return DropTable(table.TableName);
        }

        public string DropTable(string tablename)
        {
            if (tablename == null)
                return null;
            //return "DROP TABLE " + table.TableName + ";";
            return "DROP TABLE " + tablename;
        }


        public string AlterTableCreateColumn(Pixysoft.Framework.XSchEmA.Entity.ColumnSchema column)
        {
            return null;
        }

        public string AlterTableDropColumn(Pixysoft.Framework.XSchEmA.Entity.ColumnSchema column)
        {
            return null;
        }

        public string AlterTableDropColumn(string tablename, string columnname)
        {
            return null;
        }

        public string AlterTableModifyColumn(Pixysoft.Framework.XSchEmA.Entity.ColumnSchema column)
        {
            return null;
        }

        public string AlterTableModifyColumnNullable(Pixysoft.Framework.XSchEmA.Entity.ColumnSchema column)
        {
            return null;
        }

        public string AlterTableCreateConstraint(Pixysoft.Framework.XSchEmA.Entity.PrimaryKeySchema pk)
        {
            return null;
        }

        public string AlterTableDropConstraint(Pixysoft.Framework.XSchEmA.Entity.PrimaryKeySchema pk)
        {
            //只能重建表

            return null;
        }

        public string AlterTableCreateConstraint(Pixysoft.Framework.XSchEmA.Entity.ForeignKeySchema fk)
        {
            return null;
        }

        public string AlterTableDropConstraint(Pixysoft.Framework.XSchEmA.Entity.ForeignKeySchema fk)
        {
            return null;
        }

        public string AlterTableCreateConstraint(Pixysoft.Framework.XSchEmA.Entity.UniqueSchema unique)
        {
            return string.Format("CREATE UNIQUE INDEX {0} ON {1}({2})",
                unique.ConsName, unique.TableName, SchemaUtility.ListToString<string>(unique.ColumnNames));
        }

        public string AlterTableDropConstraint(Pixysoft.Framework.XSchEmA.Entity.UniqueSchema unique)
        {
            return string.Format("DROP INDEX {0}", unique.ConsName);
        }

        public string AlterTableCreateIndex(Pixysoft.Framework.XSchEmA.Entity.IndexSchema index)
        {
            return string.Format("CREATE INDEX {0} ON {1}({2})",
          index.ConsName, index.TableName, SchemaUtility.ListToString<string>(index.ColumnNames));
        }

        public string AlterTableDropIndex(Pixysoft.Framework.XSchEmA.Entity.IndexSchema index)
        {
            return string.Format("DROP INDEX {0}", index.ConsName);
        }

        public string GetDataType(DataTypeSchema datatype)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(datatype.DataType);

            if (datatype.Length > 0)
            {
                builder.Append("(" + datatype.Length.ToString() + ")");
                return builder.ToString();
            }

            if (datatype.Precision > 0)
            {
                builder.Append("(" + datatype.Precision.ToString());

                if (datatype.Scale >= 0)
                    builder.Append("," + datatype.Scale.ToString());

                builder.Append(")");

                return builder.ToString();
            }

            return builder.ToString();
        }


        public string CreateDatabase(string databasename)
        {
            return null;
        }

        public string DropDatabase(string databasename)
        {
            return null;
        }
    }
}
